﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;
using Common;

namespace DAL
{
    public class ModelConfigDAL : DB
    {
        public ModelConfigDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        public ModelConfig GetItem(string id)
        {
            ModelConfig model = new ModelConfig();
            IList<ModelConfig> list = new List<ModelConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@GUID", id) };
                DataTable dt = con.GetDataTable(@"select m.GUID,m.SchemaName,m.TableName,str(m.IsRecord) IsRecord,m.ModelTableCode,m.DefaultView,TableDescription,ModelTypeId,t.Name as TypeName,AddType,ParentModelID,Step,CONVERT(varchar, m.CreateTime,102) as CreateTime from [Working_db].[Model_Config] m 
left join [Working_db].DictModelType t on m.ModelTypeId=t.GUID where m.guid=@GUID", sqlparams);
                list = DBConvert.DataTableToList<ModelConfig>(dt);
                if (list.Count > 0)
                {
                    list[0].CreateTime = list[0].CreateTime.Replace(".", "-");
                    model = list[0];
                }
            }
            catch (Exception)
            {
                throw;
            }

            return model;
        }
        public DataTable GetModelTbItem(string mdpID, string modelID)
        {
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { 
                    new SqlParameter("@GUID", mdpID) 
                };
                return con.GetDataTable(string.Format("select * from [MDM].[{0}] where MDP_GUID=@GUID", modelID.Replace("-", "")), sqlparams);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public IList<ModelConfig> GetList(IDataParameter[] queryParas)
        {
            IList<ModelConfig> list = new List<ModelConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数 
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Config_GetList]", queryParas);
                if (dt != null && dt.Rows.Count > 0)
                {
                    ModelConfig model = null;
                    foreach (DataRow row in dt.Rows)
                    {
                        model = new ModelConfig();
                        model.GUID = row["GUID"].ToString();
                        model.TableName = row["TableName"].ToString();
                        model.TableDescription = row["TableDescription"].ToString();
                        model.IsRecord = row["IsRecord"].ToString();
                        model.Type = row["Type"].ToString();
                        model.TypeName = row["TypeName"].ToString();
                        model.vwName = row["vwName"].ToString();
                        model.Step = Convert.ToInt32(row["Step"]);
                        model.Status = Convert.ToInt32(row["Status"]);
                        model.CreateTime = row["CreateTime"].ToString();
                        model.count = row["count"].ToString();

                        list.Add(model);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        }
        public IList<ModelConfig> GetVersionList(IDataParameter[] queryParas)
        {
            IList<ModelConfig> list = new List<ModelConfig>();
            try
            {
                HIDB.dbo con = base.conn();
                string sql = @"select *,(select cast(Count(*) as varchar(10)) as count from [Working_db].[Model_Config_Record] where modelid=@ModelID
 and TableName like case when (len(@TableName)=0) then TableName else '%'+@TableName+'%' end 
 and [Version]=case when (len(@Version)=0) then [Version] else @Version end) as count from [Working_db].[Model_Config_Record] where modelid=@ModelID
 and TableName like case when (len(@TableName)=0) then TableName else '%'+@TableName+'%' end 
 and [Version]=case when (len(@Version)=0) then [Version] else @Version end order by [Version] desc 
 offset @Rows * (@PageNum-1) rows fetch next @Rows rows only";
                DataTable dt = con.GetDataTable(sql, queryParas);
                list = dt.MapToList<ModelConfig>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public DataTable GetModelDate(string modelID, string sort, string order, string[] sqlExt, IDataParameter[] para)
        {
            //创建数据库对象
            HIDB.dbo con = base.conn();
            DataTable dtResult = new DataTable();

            string orderBy = string.Empty;
            if (!string.IsNullOrEmpty(sort))
            {
                orderBy = string.Format(" ORDER BY {0} {1}", sort, order);
            }
            string sqlWhere = string.Empty;
            if (sqlExt.Length > 0)
            {
                sqlWhere = string.Format(" where {0}", string.Join(" and ", sqlExt));
            }
            string sql = string.Format("SELECT top 11 * FROM [mdm].[VW_{0}] {1} {2}", modelID, sqlWhere, orderBy);//owner.tablename

            if (para == null)
                dtResult = con.GetDataTable(sql);
            else
                dtResult = con.GetDataTable(sql, para);
            return dtResult;
        }
        public IList<ModelConfig> GetRefModelList(IDataParameter[] queryParas, string sort, string order)
        {
            IList<ModelConfig> list = new List<ModelConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数 
                string sql = string.Format(@"select m.GUID,m.TableName,m.TableDescription,t.name as TypeName,m.CreateTime,(select count(guid) from [Working_db].[Model_Config] m where m.guid=@ModelID or m.ParentModelID=@ModelID) as count 
from [Working_db].[Model_Config] m left join [Working_db].[DictModelType] t on
  m.ModelTypeId=t.GUID where m.ParentModelID=@ModelID order by {0} {1} offset @Rows * (@PageNum-1) rows fetch next @Rows rows only", sort, order);
                DataTable dt = con.GetDataTable(sql, queryParas);
                if (dt != null && dt.Rows.Count > 0)
                {
                    ModelConfig model = null;
                    foreach (DataRow row in dt.Rows)
                    {
                        model = new ModelConfig();
                        model.GUID = row["GUID"].ToString();
                        model.TableName = row["TableName"].ToString();
                        model.TableDescription = row["TableDescription"].ToString();
                        model.TypeName = row["TypeName"].ToString();
                        model.CreateTime = Convert.ToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd");
                        model.count = row["count"].ToString();

                        list.Add(model);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        }
        /// <summary>
        /// 更新模型分类
        /// </summary>
        public int UpdateModelType(Dictionary<string, object> dict_para)
        {
            int num = -1;

            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@GUID",dict_para["GUID"]),
                new SqlParameter("@Name",dict_para["Name"]),
                new SqlParameter("@Description",dict_para["Description"])
                };
                bool isUpdate = Convert.ToBoolean(dict_para["IsUpdate"]);
                if (isUpdate)
                {
                    cmd.ExecuteSql("Update [Working_db].[DictModelType] set Name=@Name,Description=@Description where GUID=@GUID", para);
                }
                else
                {
                    cmd.ExecuteSql("Insert into [Working_db].[DictModelType](GUID,Name,Description,CreateTime) values(@GUID,@Name,@Description,getdate())",
                        para);
                }

                if (!isUpdate && !string.IsNullOrEmpty(dict_para["ModelList"].ToString()))
                {
                    string[] modelIDs = dict_para["ModelList"].ToString().Split(',');
                    foreach (string id in modelIDs)
                    {
                        SqlParameter[] paraTp = new SqlParameter[] { new SqlParameter("@GUID", dict_para["GUID"]), new SqlParameter("@ModelID", id) };
                        cmd.ExecuteSql("insert into [Working_db].[Model_ConfigDictType](ModelID,TypeID) values(@ModelID,@GUID)", paraTp);
                    }
                }
                num = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return num;
        }
        /// <summary>
        /// 更新模型信息 
        /// </summary>
        public int UpdateModel(Dictionary<string, object> dict_para)
        {
            int num = -1;

            HIDB.dbo cmd = base.conn();
            try
            {
                //创建历史记录
                cmd.StoredUpdate("[Working_db].[CreateModelRecord]", new SqlParameter[] { new SqlParameter("@ModelID", dict_para["GUID"]) });
                bool isUpdate = Convert.ToBoolean(dict_para["IsUpdate"]);
                int step = 1;
                if (dict_para["AddType"].ToString() == "Reference")//引用模型
                {
                    step = 3;
                }
                if (dict_para["AddType"].ToString() == "Copy" && !isUpdate)//复制模型
                {
                    SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@ModelID",dict_para["GUID"]),
                    new SqlParameter("@RefModelID",dict_para["ModelRefID"]),
                    new SqlParameter("@TableName",dict_para["Name"]),
                    new SqlParameter("@TableDescription",dict_para["Description"]) 
                    };
                    DataTable dt = cmd.StoredDataTable("[Working_db].[Up_CopyModelConfig]", para);
                    if (dt != null && dt.Rows.Count > 0)
                        num = Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                {
                    SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@GUID",dict_para["GUID"]),
                    new SqlParameter("@Name",dict_para["Name"]),
                    new SqlParameter("@Description",dict_para["Description"]),
                    new SqlParameter("@IsRecord",dict_para["IsRecord"]),
                    new SqlParameter("@AddType",dict_para["AddType"]), 
                    new SqlParameter("@ModelRefID",dict_para["ModelRefID"]),
                    new SqlParameter("@Step",step),
                    new SqlParameter("@ModelTableCode", dict_para["GUID"].ToString().Replace("-", ""))
                    };
                    if (isUpdate)
                    {
                        cmd.ExecuteSql("Update [Working_db].[Model_Config] set TableName=@Name,IsRecord=@IsRecord,TableDescription=@Description,AddType=@AddType,ParentModelID=@ModelRefID where GUID=@GUID", para);
                    }
                    else
                    {
                        cmd.ExecuteSql(@"Insert into [Working_db].[Model_Config](GUID,SchemaName,ModelTableCode,TableName,IsRecord,TableDescription,ParentModelID,AddType,Step,DefaultView,Status,CreateTime) 
values(@GUID,'MDM',@ModelTableCode,@Name,@IsRecord,@Description,@ModelRefID,@AddType,@Step,'VW_'+@GUID,1,getdate())", para);
                    }

                    num = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return num;
        }
        /// <summary>
        /// 发送模型信息到实例 
        /// </summary>
        public int SendModelConfig(SqlParameter[] para)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                cmd.StoredUpdate("[Working_db].[Up_SendModelConfig]", para);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据GUID字段获取模型分类实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>DictionaryInfo实体对象</returns>
        public DictionaryInfo GetModelType(string guid)
        {
            IList<DictionaryInfo> list = new List<DictionaryInfo>();
            HIDB.dbo cmd = base.conn();
            IDataParameter[] sqlparams = { 
             new SqlParameter("@GUID",guid)    
            };

            try
            {
                DataTable dt = cmd.GetDataTable("select * from Working_db.DictModelType where GUID=@GUID", sqlparams);
                list = DBConvert.DataTableToList<DictionaryInfo>(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (list.Count > 0)
            {
                return list[0];
            }
            return new DictionaryInfo();
        }
        public int CheckModelName(string modelName, string id)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                IDataParameter[] sqlparams = { 
                 new SqlParameter("@TableName",modelName),
                 new SqlParameter("@GUID",id)    
                };
                DataTable dt = cmd.GetDataTable("select count(1) count from Working_db.[Model_Config] where TableName=@TableName and GUID!=@GUID", sqlparams);
                return Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 自动填充
        /// </summary> 
        /// <returns></returns>
        public IList<ModelViewConfig> GetTableInfo(string modelID, string tableName)
        {
            IList<ModelViewConfig> result = new List<ModelViewConfig>();
            try
            {
                HIDB.dbo conn = base.conn();

                string sql = @"select s.SourceSysName,v.DisplayName as TableName,c.[ColumnName] FactColumnName,v.GUID as ColumnSourceID,c.[ColumnName],
c.[ColumnType],c.[ColumnLength],c.[ColumnPrecision],c.[ColumnScale],str(c.[ColumnIsPK]) as PK,str(c.[ColumnIsNullable]) ColumnIsNullable,c.[ColumnCNName] ColumnDescription
from [Working_db].[ODS_STG_Config] f inner join [Working_db].[ETL_Source_ViewConfig] v on
f.stg_tb_name=v.mdp_viewname inner join [Working_db].[ETL_SourceSystem] s on 
v.SourceSystemid=s.guid inner join [Working_db].ETL_Source_View_ColumnConfig c on 
v.guid=c.viewid where f.ODS_ID=@ModelID order by TableName,c.ColumnOrderID";

                DataTable dt = conn.GetDataTable(sql, new SqlParameter[]
                {
                     new SqlParameter("@ModelID",modelID) 
                });
                result = Common.DBConvert.DataTableToList<ModelViewConfig>(dt);
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }
        public IList<ModelViewConfig> GetModelColumn(string modelID, string version)
        {
            IList<ModelViewConfig> result = new List<ModelViewConfig>();
            try
            {
                HIDB.dbo conn = base.conn();
                string sql = "";
                SqlParameter[] para = new SqlParameter[]
                {
                     new SqlParameter("@ModelID",modelID),
                     new SqlParameter("@Version",version)
                };
                if (string.IsNullOrEmpty(version))
                {
                    sql = @"select cast(m.ColumnOrderID as varchar(20)) as GUID,s.SourceSysName,v.DisplayName as TableName,
v.GUID as ColumnSourceID,m.ColumnName,cs.FactColumnName,m.ColumnType,m.ColumnOrderID,m.ColumnDescription,m.ColumnIsBusinessPK,m.ColumnLength,m.ColumnPrecision,m.ColumnScale 
from [Working_db].Model_ColumnConfig m inner join [Working_db].[Model_Column_SourceConfig] cs on
m.GUID=cs.ModelColumnID inner join [Working_db].ETL_Source_ViewConfig v on
substring(cs.FactColumnName,0,33)=v.mdp_viewname inner join [Working_db].ETL_SourceSystem s on
v.SourceSystemID=s.GUID where m.ModelTableID=@ModelID order by ColumnOrderID";
                }
                else
                {
                    sql = @"select cast(m.ColumnOrderID as varchar(20)) as GUID,s.SourceSysName,v.DisplayName as TableName,
v.GUID as ColumnSourceID,m.ColumnName,cs.FactColumnName,m.ColumnType,m.ColumnOrderID,m.ColumnDescription,m.ColumnIsBusinessPK,m.ColumnLength,m.ColumnPrecision,m.ColumnScale 
from [Working_db].Model_ColumnConfig_Record m inner join [Working_db].[Model_Column_SourceConfig_Record] cs on
m.ModelColumnID=cs.ModelColumnID and m.Version=cs.Version inner join [Working_db].ETL_Source_ViewConfig v on
substring(cs.FactColumnName,0,33)=v.mdp_viewname inner join [Working_db].ETL_SourceSystem s on
v.SourceSystemID=s.GUID where m.ModelTableID=@ModelID and m.Version=@Version order by ColumnOrderID";
                }
                DataTable dt = conn.GetDataTable(sql, para);
                result = Common.DBConvert.DataTableToList<ModelViewConfig>(dt);
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        public IList<ModelColumnExpression> GetColumnExpressionByModelID(string modelID, string version)
        {
            IList<ModelColumnExpression> result = new List<ModelColumnExpression>();
            try
            {
                HIDB.dbo conn = base.conn();

                string sql = "";
                if (string.IsNullOrEmpty(version))
                    sql = @"select c.ParentGUID,c.ColumnOperator,c.ColumnExpression,c.IsValue from Working_db.[ColumnExpression] c 
where c.ParentGUID in (select GUID from [Working_db].[Model_Column_ExtendConfig] where ModelTableID=@ModelID)";
                else
                    sql = @"select c.ParentGUID,c.ColumnOperator,c.ColumnExpression,c.IsValue from Working_db.[ColumnExpression_Record] c 
where c.Version=@Version and c.ParentGUID in (select ModelExtendID from [Working_db].[Model_Column_ExtendConfig_Record] where Version=@Version and ModelTableID=@ModelID)";

                DataTable dt = conn.GetDataTable(sql, new SqlParameter[]
                {
                     new SqlParameter("@ModelID",modelID),
                     new SqlParameter("@Version",version)
                });
                result = Common.DBConvert.DataTableToList<ModelColumnExpression>(dt);
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        public IList<ModelColumnExpression> GetColumnExpression(string columnid)
        {
            IList<ModelColumnExpression> result = new List<ModelColumnExpression>();
            try
            {
                HIDB.dbo conn = base.conn();

                string sql = @"select * from Working_db.[ColumnExpression] where ParentGUID=@ParentGUID";

                DataTable dt = conn.GetDataTable(sql, new SqlParameter[]
                {
                     new SqlParameter("@ParentGUID",columnid) 
                });
                result = Common.DBConvert.DataTableToList<ModelColumnExpression>(dt);
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }
        /// <summary>
        /// 删除模型标签
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteModelType(string id)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@GUID",id) 
                };
                cmd.ExecuteSql(@"DELETE [Working_db].[DictModelType] where GUID=@GUID;
Delete from [Working_db].[Model_ConfigDictType] where TypeID=@GUID", para);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int AddModelToType(SqlParameter[] para)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                cmd.ExecuteSql("insert into [Working_db].[Model_ConfigDictType](ModelID,TypeID) values(@ModelID,@TypeID)", para);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 移除标签内模型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int RemoveModelFromType(string id)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@GUID",id) 
                };
                if (id.IndexOf(",") == -1)
                    cmd.ExecuteSql("DELETE [Working_db].[Model_ConfigDictType] where ModelID=@GUID", para);
                else
                {
                    id = string.Format("'{0}'", id.Replace(",", "','"));
                    cmd.ExecuteSql("DELETE [Working_db].[Model_ConfigDictType] where ModelID in (" + id + ")");
                }
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int DeleteModel(string id)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@ModelID",id) 
                };
                cmd.StoredUpdate("[Working_db].DeleteModelConfig", para);

                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int DeleteRecord(string id, string ver)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@ModelID",id) ,
                new SqlParameter("@Version",ver) 
                };
                string sql = @"delete from [Working_db].[ColumnExpression_Record] where Version=@version and parentGUID in 
	( select ModelExtendID from [Working_db].[Model_Column_ExtendConfig_Record] where ModelTableID=@ModelID and Version=@version)
	delete from [Working_db].[Model_Column_ExtendConfig_Record]  where Version=@version and ModelTableID=@ModelID
	delete from [Working_db].[Model_Column_SourceConfig_Record]  where modelColumnID in 
	(select ModelColumnID from [Working_db].[Model_ColumnConfig_Record] where ModelTableID=@ModelID and Version=@version)
	delete from [Working_db].[Model_ColumnConfig_Record]  where Version=@version and ModelTableID=@ModelID
	delete from [Working_db].[Model_Config_Record]  where Version=@version and ModelID=@ModelID";
                cmd.ExecuteSql(sql, para);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行脚本
        /// </summary>
        /// <param name="modelID"></param>
        /// <returns></returns>
        public int RunScript(string modelID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                DataTable dt = new DataTable();
                string retVal = "";
                if (GetItem(modelID).AddType != "Reference")
                {
                    //ods
                    dt = cmd.StoredDataTable("[ODS].[FillOdsDataFromStaging_db]", new SqlParameter[] { new SqlParameter("@ODS_ID", modelID) });
                    retVal = dt.Rows[0][0].ToString();
                    if (retVal != "1")
                    {
                        return 2;
                    }

                    DataTable dqid = cmd.GetDataTable("select guid from [Working_db].[DQ_Config] where modelID=@modelID", new SqlParameter[] { new SqlParameter("@modelID", modelID) });
                    if (dqid.Rows.Count > 0)
                    {
                        SqlParameter[] sp = new SqlParameter[]
                        { 
                             new SqlParameter("@FACT_ID",dqid.Rows[0][0].ToString())
                        };
                        DataTable dqdt = cmd.StoredDataTable("[Fact].[FillFactDataFromODS_ByDQ]", sp);
                        retVal = dqdt.Rows[0][0].ToString();
                        if (retVal != "1")
                        {
                            return 3;
                        }
                    }
                }
                //创建物理表
                cmd.StoredUpdate("[Working_db].[CreateModel_CheckBack]", new SqlParameter[] { new SqlParameter("@Model_id_p", modelID) });


                //创建视图
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@Model_id_p", modelID), new SqlParameter("@viewName", "VW_" + modelID) };
                dt = cmd.StoredDataTable("[Working_db].[CreateModelViewFromModelTb]", sqlparams);
                retVal = dt.Rows[0][0].ToString();
                if (retVal != "1")
                {
                    return 5;
                }
                cmd.ExecuteSql("Update [Working_db].[Model_Config] set Step=6 where GUID=@GUID and Step=5", new SqlParameter[]
                {
                     new SqlParameter("@GUID",modelID) 
                });
                return 6;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新模型字段
        /// </summary>
        /// <param name="modelID"></param>
        /// <param name="dt"></param>
        /// <param name="dts"></param>
        /// <returns></returns>
        public int UpdateColumn(string modelID, DataTable dt, DataTable dts)
        {
            HIDB.dbo conn = base.conn();
            try
            {
                //创建历史记录
                DataTable dtStep = conn.StoredDataTable("[Working_db].[CreateModelRecord]", new SqlParameter[] { new SqlParameter("@ModelID", modelID) });

                SqlParameter[] sqlparams = new SqlParameter[]
                {
                    new SqlParameter("@ModelID",modelID)
                   ,new SqlParameter("@ModelColumn",dt)  
                   ,new SqlParameter("@ModelSourceColumn",dts)  
                };
                DataTable d = conn.StoredDataTable("[Working_db].[UP_ModelColumn]", sqlparams);
                if (d != null && d.Rows.Count > 0)
                {
                    #region 创建任务
                    if (dtStep != null && dtStep.Rows.Count > 0)
                    {
                        if (Convert.ToInt32(dtStep.Rows[0][0]) == 6)
                        {
                            ////创建物理表
                            conn.StoredUpdate("[Working_db].[CreateModel_CheckBack]", new SqlParameter[] { new SqlParameter("@Model_id_p", modelID) });
                        }
                    }
                    sqlparams = new SqlParameter[] { new SqlParameter("@ModelID", modelID) };
                    //查询是否有错误日志行数大于1执行出错
                    DataTable logDT = conn.GetDataTable("SELECT COUNT(1) AS [RowCount] FROM [Working_db].[Sys_Log] WHERE [ParentGUID] = @ModelID", sqlparams);
                    if (int.Parse(logDT.Rows[0]["RowCount"].ToString()) <= 0)
                    {
                        #region "STEP"
                        string jobStepID = Guid.NewGuid().ToString();
                        ETLSYSJobConfigStep jobStep = new ETLSYSJobConfigStep();
                        jobStep.GUID = jobStepID;
                        jobStep.JobID = EnumConvert.ConvertJopTypeToGUID(PublicEnum.JobType.TransferData);
                        jobStep.StepID = modelID;
                        jobStep.StepName = "模型配置[" + modelID + "]";
                        jobStep.StepDescription = "模型配置[" + modelID + "]";
                        jobStep.StepType = "Model";
                        jobStep.StepStatus = 1;

                        ETLSYSJobConfigStepDAL etlSYSJobConfigStepDAL = new ETLSYSJobConfigStepDAL(loginInfo);
                        ETLSYSJobConfigStepPredecessorsDAL etlPredecessorsDAL = new ETLSYSJobConfigStepPredecessorsDAL(loginInfo);
                        if (etlSYSJobConfigStepDAL.Add(jobStep))
                        {
                            FactTableDAL factTableDAL = new FactTableDAL(loginInfo);
                            ETLSYSJobConfigStepPredecessors jobStepPredecessors = null;
                            List<string> fIDs = new List<string>();
                            foreach (DataRow item in dts.Rows)
                            {
                                if (fIDs.IndexOf(item["FactTableName"].ToString()) == -1)
                                    fIDs.Add(item["FactTableName"].ToString());
                            }
                            foreach (string fid in fIDs)
                            {
                                jobStepPredecessors = new ETLSYSJobConfigStepPredecessors()
                                {
                                    GUID = Guid.NewGuid().ToString(),
                                    StepID = factTableDAL.GetFactID(fid),
                                    StepParentID = jobStepID
                                };
                                etlPredecessorsDAL.Add(jobStepPredecessors);
                            }
                            jobStepPredecessors = new ETLSYSJobConfigStepPredecessors()
                            {
                                GUID = Guid.NewGuid().ToString(),
                                StepID = jobStepID,
                                StepParentID = "0"
                            };
                            etlPredecessorsDAL.Add(jobStepPredecessors);
                        }
                        #endregion

                        conn.ExecuteSql("Update [Working_db].[Model_Config] set Step=4 where GUID=@ModelID and Step=3", sqlparams);
                    }
                    #endregion
                    return Convert.ToInt32(d.Rows[0][0]);
                }
                else
                    return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        } 
        /// <summary>
        /// 检查模型字段是否引用不存在的表
        /// </summary>
        /// <param name="modelID"></param>
        /// <param name="dt"></param> 
        /// <returns></returns>
        public string CheckModelColumn(string modelID, DataTable dt)
        {
            HIDB.dbo conn = base.conn();
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@ModelID",modelID)
                };
                DataTable dt_ods = conn.GetDataTable("select STG_TB_Name from [Working_db].[ODS_STG_Config] where ODS_ID=@ModelID", sqlparams);

                foreach (DataRow dr in dt.Rows)
                {
                    string model_ColName = dr["FactColumnName"].ToString().ToLower();
                    bool isExt = false;
                    foreach (DataRow dr_ods in dt_ods.Rows)
                    {
                        if (model_ColName.IndexOf(dr_ods[0].ToString().ToLower()) > -1)
                        {
                            isExt = true;
                            break;
                        }
                    }
                    if (!isExt)
                    {
                        return dr["ModelColumnID"].ToString();
                    }
                }
                return "1";
                 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #region"插入\编辑\删除"
        public bool Update(ModelConfigTransData data, int isDelete, string oldSchema, string oldTableName)
        {
            bool result = true;
            HIDB.dbo conn = base.conn();
            string modelTableID = data.ModelConfig.GUID;
            string sql = string.Empty;
            SqlParameter[] sqlparams = null;

            #region "获取原数据"
            //[Working_db].[Model_Config]
            sql = "SELECT * FROM [Working_db].[Model_Config] WHERE [GUID]='" + modelTableID + "'";
            IList<ModelConfig> rollBackData1 = Common.DBConvert.DataTableToList<ModelConfig>(conn.GetDataTable(sql));
            //[Working_db].[Model_Column_SourceConfig] 
            sql = "SELECT * FROM [Working_db].[Model_ColumnConfig] WHERE [ModelTableID]='" + modelTableID + "'";
            IList<ModelColumnConfig> rollBackData2 = Common.DBConvert.DataTableToList<ModelColumnConfig>(conn.GetDataTable(sql));
            //[Working_db].[Model_Column_SourceConfig]
            sql = @"SELECT a.[GUID],a.[SourceChooseOrderID] ,a.[ModelColumnID] ,a.[FactSchemaName],a.[FactTableName],a.[FactColumnName]
                            ,a.[CreateUser] ,a.[CreateTime],a.[UpdateUser] ,a.[UpdateTime] ,a.[DeleteUser] ,a.[DeleteTime] ,a.[IsDelete]
                         FROM [Working_db].[Model_Column_SourceConfig] a
                         inner join [Working_db].[Model_ColumnConfig] b on a.ModelColumnID =b.[GUID]
                         where b.ModelTableID = '" + modelTableID + "'";
            IList<ModelColumnSourceConfig> rollBackData3 = Common.DBConvert.DataTableToList<ModelColumnSourceConfig>(conn.GetDataTable(sql));
            //[Working_db].[Model_Column_ExtendConfig]
            sql = @"SELECT * FROM [Working_db].[Model_Column_ExtendConfig] where ModelTableID = '" + modelTableID + "'";
            IList<ModelColumnExtendConfig> rollBackData4 = Common.DBConvert.DataTableToList<ModelColumnExtendConfig>(conn.GetDataTable(sql));
            #endregion

            try
            {
                #region "准备数据"
                IList<ModelConfig> listModelConfig = new List<ModelConfig>();
                listModelConfig.Add(data.ModelConfig);
                DataTable dtModelConfig = Common.DBConvert.ConvertToDataTable<ModelConfig>(listModelConfig);

                //if (data.ModelColumnConfigList != null && data.ModelColumnConfigList.Count > 0)
                //{
                //    foreach (var item in data.ModelColumnConfigList)
                //    {
                //        string[] typeArr = item.ColumnType.Replace(")", "").Split('(');
                //        item.ColumnType = typeArr[0].ToLower();
                //        if (typeArr.Length > 1)
                //        {
                //            string[] lengthArr = typeArr[1].Split(',');
                //            if (lengthArr.Length == 1)
                //            {
                //                if (lengthArr[0].ToUpper().Equals("MAX"))
                //                {
                //                    item.ColumnLength = -1;
                //                }
                //                else
                //                {
                //                    item.ColumnLength = int.Parse(lengthArr[0]);
                //                }
                //            }
                //            else
                //            {
                //                if (item.ColumnType.Equals("numeric") || item.ColumnType.Equals("decimal"))
                //                {
                //                    item.ColumnLength = int.Parse(lengthArr[0]);
                //                    item.ColumnPrecision = int.Parse(lengthArr[0]);
                //                    item.ColumnScale = int.Parse(lengthArr[1]);
                //                }
                //                else
                //                {

                //                }
                //            }
                //        }
                //    }
                //}
                DataTable dtModelColumnConfig = Common.DBConvert.ConvertToDataTable<ModelColumnConfig>(data.ModelColumnConfigList);
                DataTable dtModelColumnSourceConfig = Common.DBConvert.ConvertToDataTable<ModelColumnSourceConfig>(data.ModelColumnSourceConfigList);
                #endregion

                #region "执行删除操作时同时删除物理表"
                if (isDelete == 1)
                {
                    conn.StoredUpdate("[Working_db].[DropModelTable]", new SqlParameter[] { new SqlParameter("@Model_id", modelTableID) });
                }
                #endregion

                #region "重命名架构和表名"
                if (isDelete == 0 && !string.IsNullOrEmpty(oldTableName))
                {
                    sqlparams = new SqlParameter[]
                    {
                        new SqlParameter("@GUID",modelTableID)
                       ,new SqlParameter("@NewSchemaName","")
                       ,new SqlParameter("@NewTableName",data.ModelConfig.TableName)
                    };
                    conn.StoredUpdate("[Working_db].[UP_Model_ReName]", sqlparams);
                }
                #endregion

                #region "执行/更新/新增/删除"
                sqlparams = new SqlParameter[]
                {
                    new SqlParameter("@Model_tb",dtModelConfig)
                   ,new SqlParameter("@ModelColumn",dtModelColumnConfig)
                   ,new SqlParameter("@ModelColumnSource",dtModelColumnSourceConfig)
                   ,new SqlParameter("@isdelete",isDelete)
                };
                //结果为1执行成功 
                if (conn.StoredDataTable("[Working_db].[UP_Model_Operate]", sqlparams).Rows[0]["Result"].ToString().Equals("1"))
                {
                    if (isDelete == 0)
                    {
                        #region "注释代码"
                        //ModelColumnSourceConfig _temp = data.ModelColumnSourceConfigList[0];
                        //string sql = string.Format(@"if(OBJECT_ID('[{0}].[{1}]')) is not null
                        //                                             begin
                        //	                                              truncate table [{0}].[{1}]
                        //                                              end", _temp.FactSchemaName, _temp.FactTableName);
                        //conn.ExecuteSql(sql);

                        //conn.StoredUpdate("[Working_db].[CreateModelFromFact]", new SqlParameter[] { new SqlParameter("@Model_id_p", data.ModelConfig.GUID) });

                        //sqlparams = new SqlParameter[] 
                        //{ 
                        //         new SqlParameter("@newSchema",data.ModelConfig.TableType)
                        //        , new SqlParameter("@oldSchema",oldSchema)
                        //        , new SqlParameter("@objectName",data.ModelConfig.TableName)
                        // };
                        //conn.StoredUpdate("[Working_db].[Transfer_Schema]", sqlparams);
                        #endregion
                        //创建物理表
                        conn.StoredUpdate("[Working_db].[CreateModel_CheckBack]", new SqlParameter[] { new SqlParameter("@Model_id_p", modelTableID) });

                        sqlparams = new SqlParameter[] { new SqlParameter("@ModelID", modelTableID) };
                        //查询是否有错误日志行数大于1执行出错
                        if (int.Parse(conn.GetDataTable("SELECT COUNT(1) AS [RowCount] FROM [Working_db].[Sys_Log] WHERE [ParentGUID] = @ModelID", sqlparams).Rows[0]["RowCount"].ToString()) <= 0)
                        {
                            #region "STEP"
                            ETLSYSJobConfigStep jobStep = new ETLSYSJobConfigStep();
                            jobStep.GUID = Guid.NewGuid().ToString();
                            jobStep.JobID = EnumConvert.ConvertJopTypeToGUID(PublicEnum.JobType.TransferData);
                            jobStep.StepID = data.ModelConfig.GUID;
                            jobStep.StepName = "模型配置[" + data.ModelConfig.TableName + "]";
                            jobStep.StepDescription = "模型配置[" + data.ModelConfig.TableName + "]";
                            jobStep.StepType = "Model";
                            jobStep.StepStatus = 1;

                            ETLSYSJobConfigStepDAL etlSYSJobConfigStepDAL = new ETLSYSJobConfigStepDAL(loginInfo);
                            ETLSYSJobConfigStepPredecessorsDAL etlPredecessorsDAL = new ETLSYSJobConfigStepPredecessorsDAL(loginInfo);
                            if (etlSYSJobConfigStepDAL.Add(jobStep))
                            {
                                FactTableDAL factTableDAL = new FactTableDAL(loginInfo);
                                jobStep = etlSYSJobConfigStepDAL.GetItemByStepID(data.ModelConfig.GUID);
                                ETLSYSJobConfigStepPredecessors jobStepPredecessors = null;
                                foreach (var item in data.ModelColumnSourceConfigList)
                                {
                                    jobStepPredecessors = new ETLSYSJobConfigStepPredecessors() { GUID = Guid.NewGuid().ToString(), StepID = factTableDAL.GetFactID(item.FactTableName), StepParentID = jobStep.GUID };
                                    etlPredecessorsDAL.Add(jobStepPredecessors);
                                    break;
                                }

                                jobStepPredecessors = new ETLSYSJobConfigStepPredecessors() { GUID = Guid.NewGuid().ToString(), StepID = jobStep.GUID, StepParentID = "0" };
                                etlPredecessorsDAL.Add(jobStepPredecessors);
                            }
                            #endregion
                            //创建视图
                            CreateView(modelTableID, data.ModelConfig.DefaultView);
                        }
                        else
                        {
                            //执行回滚
                            RollBack(modelTableID, rollBackData1, rollBackData2, rollBackData3, rollBackData4);
                            result = false;
                        }
                    }
                }
                else
                {
                    //执行回滚
                    RollBack(modelTableID, rollBackData1, rollBackData2, rollBackData3, rollBackData4);
                    result = false;
                }
                #endregion
            }
            catch (Exception)
            {
                //执行回滚
                RollBack(modelTableID, rollBackData1, rollBackData2, rollBackData3, rollBackData4);
                result = false;
                throw;
            }
            return result;
        }

        public DataTable CheckDelete(string guid)
        {
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                    new  SqlParameter("@GUID",guid)
                };
                return con.StoredDataTable("[Working_db].[UP_Check_Model_Delete]", sqlparams);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool RepeatColumnCheck(string name)
        {
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@name", name) };
                DataTable dt = con.GetDataTable("select distinct a.KeyWord from [Working_db].[DBKeyWord] a where a.KeyWord=@name", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #region "模型回滚"
        private void RollBack(string modelTableID, IList<ModelConfig> rollBackData1, IList<ModelColumnConfig> rollBackData2, IList<ModelColumnSourceConfig> rollBackData3, IList<ModelColumnExtendConfig> rollBackData4)
        {
            try
            {
                HIDB.dbo conn = base.conn();
                string sql = string.Empty;

                #region "还原原数据"
                SqlParameter[] sqlparams = null;
                if (rollBackData1 != null && rollBackData1.Count > 0)
                {
                    sql = @"UPDATE [Working_db].[Model_Config]
                                 SET  [SchemaName]=@SchemaName
                                        ,[TableName] = @TableName
                                        ,[ModelTableCode]=@ModelTableCode
                                        ,[TableDescription]=@TableDescription
                                        ,[ModelTypeId]=@ModelTypeId
                                        ,[TableType]=@TableType
                                        ,[DefaultView]=@DefaultView
                                        ,[CreateUser]=@CreateUser
                                        ,[CreateTime]=@CreateTime
                                 WHERE [GUID] = @GUID";
                    ModelConfig _temp = rollBackData1[0];
                    sqlparams = new SqlParameter[]
                    {
                         new SqlParameter("@GUID",_temp.GUID) 
                        ,new SqlParameter("@TableName",_temp.TableName) 
                        ,new SqlParameter("@TableDescription",_temp.TableDescription) 
                        ,new SqlParameter("@DefaultView",_temp.DefaultView) 
                        ,new SqlParameter("@CreateTime",_temp.CreateTime)
                    };
                    conn.ExecuteSql(sql, sqlparams);
                    sqlparams = new SqlParameter[]
                    {
                         new SqlParameter("@GUID",modelTableID) 
                        ,new SqlParameter("@NewTableName",_temp.TableName)
                    };
                    conn.StoredUpdate("[Working_db].[UP_Model_ReName]", sqlparams);
                    CreateView(modelTableID, _temp.DefaultView);
                }
                else
                {
                    sql = "delete  from [Working_db].[Model_Config] where [GUID]='" + modelTableID + "'";
                    conn.ExecuteSql(sql);

                    conn.StoredUpdate("[Working_db].[DropModelTable]", new SqlParameter[] { new SqlParameter("@Model_id", modelTableID) });
                }

                sql = "delete  from [Working_db].[Model_ColumnConfig] where [ModelTableID]='" + modelTableID + "'";
                conn.ExecuteSql(sql);

                sql = @"delete a from [Working_db].[Model_Column_SourceConfig] a 
                             inner join [Working_db].[Model_ColumnConfig] b on a.ModelColumnID=b.[GUID]
                             where b.ModelTableID ='" + modelTableID + "'";
                conn.ExecuteSql(sql);

                sql = "delete from [Working_db].[Model_Column_ExtendConfig]   where ModelTableID='" + modelTableID + "'";
                conn.ExecuteSql(sql);

                if (rollBackData2 != null && rollBackData2.Count > 0)
                {
                    foreach (ModelColumnConfig item in rollBackData2)
                    {
                        sqlparams = new SqlParameter[]
                        {
                             new SqlParameter("@GUID",item.GUID)
                            ,new SqlParameter("@ModelTableID",item.ModelTableID)
                            ,new SqlParameter("@ColumnOrderID",item.ColumnOrderID)
                            ,new SqlParameter("@ColumnName",item.ColumnName)
	                        ,new SqlParameter("@ColumnDescription",item.ColumnDescription)
                            ,new SqlParameter("@ColumnType",item.ColumnType)
                            ,new SqlParameter("@ColumnLength",item.ColumnLength)
                            ,new SqlParameter("@ColumnPrecision",item.ColumnPrecision)
                            ,new SqlParameter("@ColumnScale",item.ColumnScale)
                            ,new SqlParameter("@ColumnIsBusinessPK ",item.ColumnIsBusinessPK)
                            ,new SqlParameter("@ColumnIsNullable",item.ColumnIsNullable)
                            ,new SqlParameter("@ColumnParendID",item.ColumnParendID)
                            ,new SqlParameter("@ColumnDefaultValue",item.ColumnDefaultValue)
                            ,new SqlParameter("@ColumnFixValue",item.ColumnFixValue)
                            ,new SqlParameter("@CreateUser",item.CreateUser)
                            ,new SqlParameter("@CreateTime",item.CreateTime)
                        };
                        conn.StoredUpdate("[Working_db].[UP_Model_ColumnConfig_Insert]", sqlparams);
                    }
                }

                if (rollBackData3 != null && rollBackData3.Count > 0)
                {
                    foreach (ModelColumnSourceConfig item in rollBackData3)
                    {
                        sqlparams = new SqlParameter[]
                        {
                             new SqlParameter("@GUID",item.GUID)
                            ,new SqlParameter("@SourceChooseOrderID",item.SourceChooseOrderID)
                            ,new SqlParameter("@ModelColumnID",item.ModelColumnID)
	                        ,new SqlParameter("@FactSchemaName",item.FactSchemaName)
                            ,new SqlParameter("@FactTableName",item.FactTableName)
                            ,new SqlParameter("@FactColumnName",item.FactColumnName)
                            ,new SqlParameter("@CreateUser",item.CreateUser)
                            ,new SqlParameter("@CreateTime",item.CreateTime)
                        };
                        conn.StoredUpdate("[Working_db].[UP_Model_Column_SourceConfig_Insert]", sqlparams);
                    }
                }

                if (rollBackData4 != null && rollBackData4.Count > 0)
                {
                    foreach (ModelColumnExtendConfig item in rollBackData4)
                    {
                        sqlparams = new SqlParameter[]
                        {
                             new SqlParameter("@GUID",item.GUID)
                            ,new SqlParameter("@ModelTableID",item.ModelTableID)
                            ,new SqlParameter("@ColumnName",item.ColumnName)
	                        ,new SqlParameter("@ColumnScriptExtend",item.ColumnScriptExtend)
                            ,new SqlParameter("@CreateUser",item.CreateUser)
                        };
                        conn.StoredUpdate("[Working_db].[UP_Model_Column_ExtendConfig_Insert]", sqlparams);
                    }
                }
                #endregion
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

        #region "查询单个实体对象"


        public string GetFactTableName(string modelTableID)
        {
            string result = string.Empty;
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@ModelTableID", modelTableID) };
                string sql = @" select top 1 b.[FactTableName] from [Working_db].[Model_ColumnConfig] a
                                inner join [Working_db].[Model_Column_SourceConfig] b on a.[GUID] = b.ModelColumnID
                                where a.ModelTableID = @ModelTableID ";
                HIDB.dbo con = base.conn();
                DataTable dt = con.GetDataTable(sql, sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = dt.Rows[0]["FactTableName"].ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public IList<ModelColumnConfigTransData> GetTransDataList(string modelTableID)
        {
            IList<ModelColumnConfigTransData> result = new List<ModelColumnConfigTransData>();
            try
            {
                IList<ModelColumnConfig> data1 = GetModelColumnConfigItems(modelTableID);
                if (data1.Count > 0)
                {
                    ModelColumnConfigTransData model = null;
                    foreach (var item in data1)
                    {
                        model = new ModelColumnConfigTransData();
                        model.ColumnName = item.ColumnName;
                        model.ColumnDescription = item.ColumnDescription;
                        model.ColumnType = item.ColumnType;
                        model.ColumnIsBusinessPK = item.ColumnIsBusinessPK;
                        model.IsNull = item.ColumnIsNullable;
                        model.ColumnDefaultValue = item.ColumnDefaultValue;
                        model.ColumnFixValue = item.ColumnFixValue;

                        if (!string.IsNullOrEmpty(item.ColumnParendID))
                        {
                            model.ColumnParendID = data1.Where(p => p.GUID.Equals(item.ColumnParendID)).FirstOrDefault().ColumnName;
                        }
                        else
                        {
                            model.ColumnParendID = "";
                        }

                        IList<ModelColumnSourceConfig> data2 = GetModelColumnSourceConfigItems(item.GUID);
                        if (data2.Count > 0)
                        {
                            model.FactTableName = data2[0].FactTableName;
                            switch (data2.Count)
                            {
                                case 1:
                                    model.FactColumnName1 = data2[0].FactColumnName;
                                    break;
                                case 2:
                                    model.FactColumnName1 = data2[0].FactColumnName;
                                    model.FactColumnName2 = data2[1].FactColumnName;
                                    break;
                                case 3:
                                    model.FactColumnName1 = data2[0].FactColumnName;
                                    model.FactColumnName2 = data2[1].FactColumnName;
                                    model.FactColumnName3 = data2[2].FactColumnName;
                                    break;
                                default:
                                    break;
                            }
                        }
                        result.Add(model);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        private IList<ModelColumnConfig> GetModelColumnConfigItems(string modelTableID)
        {
            IList<ModelColumnConfig> result = new List<ModelColumnConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@ModelTableID", modelTableID) };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_ColumnConfig_GetItems]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    ModelColumnConfig model = null;
                    foreach (DataRow row in dt.Rows)
                    {
                        model = new ModelColumnConfig();
                        model.GUID = row["GUID"].ToString();
                        model.ColumnName = row["ColumnName"].ToString();
                        model.ColumnDescription = row["ColumnDescription"].ToString();
                        model.ColumnType = FomatColumnType(row["ColumnType"].ToString(), row["ColumnLength"].ToString(), row["ColumnPrecision"].ToString(), row["ColumnScale"].ToString());
                        model.ColumnIsBusinessPK = int.Parse(row["ColumnIsBusinessPK"].ToString());
                        model.ColumnIsNullable = int.Parse(row["ColumnIsNullable"].ToString());
                        model.ColumnDefaultValue = row["ColumnDefaultValue"].ToString();
                        model.ColumnFixValue = row["ColumnFixValue"].ToString();
                        model.ColumnParendID = row["ColumnParendID"].ToString();

                        result.Add(model);
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        private IList<ModelColumnSourceConfig> GetModelColumnSourceConfigItems(string modelColumnID)
        {
            IList<ModelColumnSourceConfig> result = new List<ModelColumnSourceConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = { new SqlParameter("@ModelColumnID", modelColumnID) };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Column_SourceConfig_GetItems]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    ModelColumnSourceConfig model = null;
                    foreach (DataRow row in dt.Rows)
                    {
                        model = new ModelColumnSourceConfig();
                        model.GUID = row["GUID"].ToString();

                        if (row["FactSchemaName"] != DBNull.Value)
                        {
                            if (!string.IsNullOrEmpty(row["FactSchemaName"].ToString()))
                            {
                                model.FactSchemaName = row["FactSchemaName"].ToString();
                            }
                            else
                            {
                                model.FactSchemaName = "";
                            }
                        }
                        else
                        {
                            model.FactSchemaName = "";
                        }

                        if (row["FactTableName"] != DBNull.Value)
                        {
                            if (!string.IsNullOrEmpty(row["FactTableName"].ToString()))
                            {
                                model.FactTableName = row["FactTableName"].ToString();
                            }
                            else
                            {
                                model.FactTableName = "";
                            }
                        }
                        else
                        {
                            model.FactTableName = "";
                        }

                        if (row["FactColumnName"] != DBNull.Value)
                        {
                            if (!string.IsNullOrEmpty(row["FactColumnName"].ToString()))
                            {
                                model.FactColumnName = row["FactColumnName"].ToString();
                            }
                            else
                            {
                                model.FactColumnName = "";
                            }
                        }
                        else
                        {
                            model.FactColumnName = "";
                        }

                        result.Add(model);
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }
        #endregion

        #region "查询集合"
        public IList<ModelConfig> GetList(string modelTypeId, string tableName, int pageSize, int pageIndex)
        {
            IList<ModelConfig> list = new List<ModelConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@ModelTypeId",string.IsNullOrEmpty(modelTypeId)?DBNull.Value.ToString():modelTypeId)
                    ,new  SqlParameter("@TableName",string.IsNullOrEmpty(tableName)?DBNull.Value.ToString():tableName)
                    ,new  SqlParameter("@pageSize",pageSize)
                    ,new  SqlParameter("@pageIndex",pageIndex)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Config_GetList]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    ModelConfig model = null;
                    foreach (DataRow row in dt.Rows)
                    {
                        model = new ModelConfig();
                        model.GUID = row["GUID"].ToString();
                        model.TableName = row["TableName"].ToString();
                        model.TableDescription = row["TableDescription"].ToString();
                        model.DefaultView = row["DefaultView"].ToString();
                        list.Add(model);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;
        }

        public int GetListCount(string modelTypeId, string tableName)
        {
            int result = 0;
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@ModelTypeId",string.IsNullOrEmpty(modelTypeId)?DBNull.Value.ToString():modelTypeId)
                    ,new  SqlParameter("@TableName",string.IsNullOrEmpty(tableName)?DBNull.Value.ToString():tableName)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Config_GetListCount]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = Int32.Parse(dt.Rows[0]["RowsCount"].ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion


        #region "更新视图名称"
        public bool CreateView(string id, string viewName)
        {
            bool result = true;
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@Model_id_p", id), new SqlParameter("@viewName", viewName) };
                result = con.StoredDataTable("[Working_db].[CreateModelViewFromModelTb]", sqlparams).Rows[0][0].ToString().Equals("1") ? true : false;

            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }
        #endregion

        /// <summary>
        /// 获取视图信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ModelViewConfig GetViewInfo(string id)
        {
            string result = string.Empty;
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = { new SqlParameter("@GUID", id) };
                DataTable dt = con.GetDataTable("select ColumnScriptExtend from [Working_db].[Model_Column_ExtendConfig] where GUID=@GUID", sqlparams);
                IList<ModelViewConfig> model = new List<ModelViewConfig>();
                model = Common.DBConvert.DataTableToList<ModelViewConfig>(dt);
                if (model.Count > 0)
                    return model[0];
                else
                    return new ModelViewConfig();
            }
            catch (Exception)
            {
                throw;
            }
        }

        #region "查询实体列集合"
        public IList<ModelColumnConfig> GetModelColumns(string modelTableID)
        {
            IList<ModelColumnConfig> result = new List<ModelColumnConfig>();
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                //添加输入输出参数
                SqlParameter[] sqlparams = { new SqlParameter("@ModelTableID", modelTableID) };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_ColumnConfig_GetColumns]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    ModelColumnConfig model = null;
                    foreach (DataRow row in dt.Rows)
                    {
                        model = new ModelColumnConfig();
                        model.ColumnName = row["ColumnName"].ToString();
                        model.ColumnDescription = row["ColumnDescription"].ToString();
                        model.ColumnOperator = "=";
                        model.ColumnValue = "";

                        result.Add(model);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        #region "检查ModelConfig视图名称是否存在"
        public bool ViewNameIsExist(string viewName, string id)
        {
            bool result = false;
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@ViewName",viewName)
                    ,new  SqlParameter("@GUID",id)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Config_ViewName_IsExist]", sqlparams);
                result = Int32.Parse(dt.Rows[0]["RowsCount"].ToString()) > 0 ? true : false;
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        #region "检查ModelConfig实体名称是否存在"
        public int TableNameIsExist(string tableName, string modelTableCode, string id)
        {
            int result = 0;
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@TableName",tableName)
                    ,new SqlParameter("@ModelTableCode",modelTableCode)
                    ,new  SqlParameter("@GUID",id)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_Model_Config_TableName_IsExist]", sqlparams);
                result = Int32.Parse(dt.Rows[0][0].ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        public IList<string> GetModelTableNameList()
        {
            IList<string> result = new List<string>();
            HIDB.dbo con = base.conn();
            DataTable dt = con.GetDataTable("SELECT  [TableName] FROM [Working_db].[Model_Config]");
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    result.Add(item[0].ToString());
                }
            }
            return result;
        }

        private string FomatColumnType(string columnType, string columnLength, string columnPrecision, string columnScale)
        {
            string result = string.Empty;
            try
            {
                if (columnType.Equals("binary") || columnType.Equals("char") || columnType.Equals("datetime2") || columnType.Equals("datetimeoffset") || columnType.Equals("nchar")
                      || columnType.Equals("time") || columnType.Equals("varbinary") || columnType.Equals("varchar"))
                {
                    result = string.Format("{0}({1})", columnType, columnLength);
                }
                else if (columnType.Equals("nvarchar"))
                {
                    if (columnLength.Equals("-1"))
                    {
                        result = "nvarchar(max)";
                    }
                    else
                    {
                        result = string.Format("{0}({1})", columnType, columnLength);
                    }
                }
                else if (columnType.Equals("uniqueidentifier"))
                {
                    result = "nvarchar(max)";
                }
                else if (columnType.Equals("numeric") || columnType.Equals("decimal"))
                {
                    result = string.Format("{0}({1},{2})", columnType, columnPrecision, columnScale);
                }
                else
                {
                    result = columnType;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        public DataTable CheckModelScript(string text)
        {
            string result = string.Empty;
            try
            {
                HIDB.dbo con = base.DBReadOnly();
                return con.GetDataTable(string.Format("select {0}", text));
            }
            catch (Exception)
            {
                throw;
            }
        }
        #region 模型分类关联表 Model_ConfigDictType

        /// <summary>
        /// 根据GUID字段获取模型分类实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>DictionaryInfo实体对象</returns>
        public List<DictionaryInfo> GetModelTypesByModelID(string modelID)
        {
            List<DictionaryInfo> result = new List<DictionaryInfo>();
            //DataTable dt = cmd.GetDataTable("select * from Working_db.DictModelType where GUID=@GUID", sqlparams);
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = @"SELECT * FROM [Working_db].[DictModelType]
LEFT JOIN [Working_db].[Model_ConfigDictType]
ON [Working_db].[DictModelType].GUID=[Working_db].[Model_ConfigDictType].TypeID
WHERE [Working_db].[Model_ConfigDictType].ModelID=@ModelID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ModelID",modelID)
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<DictionaryInfo>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public List<string> GetTypeIDsByModelID(string modelID)
        {
            List<string> result = new List<string>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = "SELECT TypeID FROM [Working_db].[Model_ConfigDictType] WHERE ModelID=@ModelID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ModelID",modelID)
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToStringList("TypeID");
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public int AddModelTypeID(string modelID, string typeID)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"INSERT INTO [Working_db].[Model_ConfigDictType]  (ModelID,TypeID) 
VALUES(@ModelID,@TypeID)";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ModelID",modelID)
                     ,new SqlParameter("@TypeID",typeID)
                     
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("AddModelTypeID", "AddModelTypeID", 0, 1, ex, "", "");
            }
            return num;
        }

        public int UpdateModelTypeID(string modelID, string typeID, string oldtypeID)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"UPDATE [Working_db].[Model_ConfigDictType] 
SET ModelID=@ModelID,TypeID=@TypeID 
WHERE ModelID=@ModelID AND TypeID=@OldTypeID)";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ModelID",modelID)
                     ,new SqlParameter("@TypeID",typeID)
                     ,new SqlParameter("@OldTypeID",oldtypeID)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("AddModelTypeID", "AddModelTypeID", 0, 1, ex, "", "");
            }
            return num;
        }

        public int DeleteModelTypeID(string modelID, string typeID)
        {
            //"working_db.[UP_ETL_DbConfig_Insert]", sqlparams);
            //创建cmd对象
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"DELETE FROM [Working_db].[Model_ConfigDictType] 
WHERE ModelID=@ModelID AND TypeID=@TypeID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ModelID",modelID)
                     ,new SqlParameter("@TypeID",typeID)
                     
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("DeleteModelTypeID", "DeleteModelTypeID", 0, 1, ex, "", "");
            }
            return num;
        }

        public int DeleteModelTypeByModelID(string modelID)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE FROM [Working_db].[Model_ConfigDictType] where ModelID=@ModelID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ModelID",modelID)
                    // ,new SqlParameter("@SysType",1)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }

        #endregion
    }
}
